﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace GSManager
{
    public class SanPham
    {
        private string _MaSP;
        private string _TenSP;
        private string _MaLoaiSP;
        private int _SoLuong;
        private string _MoTa;
        private decimal _GiaNhap;
        private decimal _GiaBanLe;
        private string _DonViTinh;
        private int _TonToiThieu;
        private string _HinhAnh;

        public string MaSP
        {
            get { return _MaSP; }
            set { _MaSP = value; }
        }
        public string TenSP
        {
            get { return _TenSP; }
            set { _TenSP = value; }
        }
        public string MaLoaiSP
        {
            get { return _MaLoaiSP; }
            set { _MaLoaiSP = value; }
        }
        public int SoLuong
        {
            get { return _SoLuong; }
            set { _SoLuong = value; }
        }
        public string MoTa
        {
            get { return _MoTa; }
            set { _MoTa = value; }
        }
        public decimal GiaNhap
        {
            get { return _GiaNhap; }
            set { _GiaNhap = value; }
        }
        public decimal GiaBanLe
        {
            get { return _GiaBanLe; }
            set { _GiaBanLe = value; }
        }
        public string DonViTinh
        {
            get { return _DonViTinh; }
            set { _DonViTinh = value; }
        }
        public int TonToiThieu
        {
            get { return _TonToiThieu; }
            set { _TonToiThieu = value; }
        }
        public string HinhAnh
        {
            get { return _HinhAnh; }
            set { _HinhAnh = value; }
        }

        public void ThemSanPham()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "Insert into SANPHAM values(?,?,?,?,?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@MaSanPham", OleDbType.VarChar);
            cmd.Parameters.Add("@TenSanPham", OleDbType.VarWChar);
            cmd.Parameters.Add("@MaLoaiSanPham", OleDbType.VarChar);
            cmd.Parameters.Add("@SoLuong", OleDbType.Integer);
            cmd.Parameters.Add("@MoTa", OleDbType.VarWChar);
            cmd.Parameters.Add("@GiaNhap", OleDbType.Decimal);
            cmd.Parameters.Add("@GiaBanLe", OleDbType.Decimal);
            cmd.Parameters.Add("@DonViTinh", OleDbType.VarWChar);
            cmd.Parameters.Add("@TonToiThieu", OleDbType.Integer);
          
            cmd.Parameters[0].Value = MaSP;
            cmd.Parameters[1].Value = TenSP;
            cmd.Parameters[2].Value = MaLoaiSP;
            cmd.Parameters[3].Value = SoLuong;
            cmd.Parameters[4].Value = MoTa;
            cmd.Parameters[5].Value = GiaNhap;
            cmd.Parameters[6].Value = GiaBanLe;
            cmd.Parameters[7].Value = DonViTinh;
            cmd.Parameters[8].Value = TonToiThieu;
      

            cmd.ExecuteNonQuery();
            connect.Close();
        }
        public void XoaSanPham(string masp)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "Delete from SANPHAM where MaSanPham='" + masp + "'";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.ExecuteNonQuery();
            connect.Close();
        }
        public void CapNhat()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "Update SANPHAM SET GiaNhap=?,GiaBanLe=?,DonViTinh=?,TonToiThieu=?,SoLuong=? where MaSanPham=?";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@GiaNhap", OleDbType.Decimal);
            cmd.Parameters[0].Value = GiaNhap;
            cmd.Parameters.Add("@GiaBanLe", OleDbType.Decimal);
            cmd.Parameters[1].Value = GiaBanLe;
            cmd.Parameters.Add("@DonViTinh", OleDbType.VarWChar);
            cmd.Parameters[2].Value = DonViTinh;
            cmd.Parameters.Add("@TonToiThieu", OleDbType.Integer);
            cmd.Parameters[3].Value = TonToiThieu;
            cmd.Parameters.Add("@SoLuong", OleDbType.Integer);
            cmd.Parameters[4].Value = SoLuong;
            cmd.Parameters.Add("@MaSanPham", OleDbType.VarChar);
            cmd.Parameters[5].Value = MaSP;
            cmd.ExecuteNonQuery();
        }
        public DataSet LayDanhSach()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select * from SANPHAM";
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connect);
            DataSet ds = new DataSet();
            adapter.Fill(ds, "SANPHAM");
            connect.Close();
            return ds;
        }
        public DataSet LayDanhSachDeCapNhat()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select MaSanPham,TenSanPham,GiaNhap,GiaBanLe,SoLuong,TonToiThieu from SANPHAM";
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connect);
            DataSet ds = new DataSet();
            adapter.Fill(ds, "SANPHAM");
            connect.Close();
            return ds;
        }
        public System.Windows.Forms.AutoCompleteStringCollection LayDSTenSanPham()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select TenSanPham from SANPHAM";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbDataReader r = cmd.ExecuteReader();
            System.Windows.Forms.AutoCompleteStringCollection list = new System.Windows.Forms.AutoCompleteStringCollection();
            while (r.Read())
            {
                string k = (string)r[0];
                list.Add(k);
            }
            r.Close();
            connect.Close();
            return list;
        }
        public SanPham LaySanPhamTheoTen(string tensp)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select * from SANPHAM where TenSanPham=?";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@TenSanPham", OleDbType.VarWChar);
            cmd.Parameters[0].Value = tensp; 
            OleDbDataReader r = cmd.ExecuteReader();
            SanPham sanpham = new SanPham() ;
            while (r.Read())
            {
               
                sanpham.MaSP=(string)r["MaSanPham"];
                sanpham.TenSP=(string)r["TenSanPham"];
                sanpham.DonViTinh=(string)r["DonViTinh"];
                sanpham.GiaBanLe=(decimal)r["GiaBanLe"];
                sanpham.GiaNhap = (decimal)r["GiaNhap"];
                sanpham.SoLuong = (int)r["SoLuong"];
                sanpham.MoTa = (string)r["MoTa"];
                sanpham.TonToiThieu = (int)r["TonToiThieu"];
                sanpham.MaLoaiSP = (string)r["MaLoaiSanPham"];
            }
           r.Close();
            connect.Close();
            return sanpham;
        }
     /*   public List<SanPham> LayDS()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select * from SANPHAM";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            OleDbDataReader r = cmd.ExecuteReader();
            List<SanPham> list = new List<SanPham>();
            SanPham sanpham;
            while (r.Read())
            {
                sanpham = new SanPham();
                sanpham.MaLoaiSP = (string)r["MaLoaiSanPham"];
                sanpham.MaSP = (string)r["MaSanPham"];
                sanpham.TenSP = (string)r["TenSanPham"];
                sanpham.MoTa = (string)r["MoTa"];
                sanpham.SoLuong = (int)r["SoLuong"];
                sanpham.DonViTinh = (string)r["DonViTinh"];
                sanpham.GiaBanLe = (string)r["GiaBanLe"];
            }
        }*/
        public DataSet LayDanhSachTheoLoai(string loaisp)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select * from SANPHAM where MaLoaiSanPham='"+loaisp+"'";
          
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connect);
            DataSet ds = new DataSet();
            adapter.Fill(ds, "SANPHAM");
            connect.Close();
            return ds;
        }
        public int DemSoSanPhamCungLoai()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select Count(MaSanPham) from SANPHAM where MaLoaiSanPham=?";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@MaLoaiSanPham", OleDbType.VarChar);
            cmd.Parameters[0].Value = MaLoaiSP;
            OleDbDataReader r = cmd.ExecuteReader();
            int kq = 0;
            while (r.Read())
            {
                kq = (int)r[0];
            }
            r.Close();
            connect.Close();
            return kq;
        }
        public bool KiemTraTenSanPham()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select MaSanPham from SANPHAM where TenSanPham=?";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@TenSanPham", OleDbType.VarWChar);
            cmd.Parameters[0].Value = TenSP;
            OleDbDataReader r = cmd.ExecuteReader();
            bool kq = r.Read();
            return kq;
        }
        
    }
}
